package com.lt.member;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;

import code.lewdy.app.user.AccountRecord;
import code.lewdy.db.Database;
import code.lewdy.db.HibernateTemple;
import code.lewdy.db.JdbcTemple;
import code.lewdy.db.JdbcUtil;
import code.lewdy.util.FormatUtil;
import code.lewdy.web.session.HttpSessionAttributeListenerImpl;
import dao.HibernateSessionFactory;

public class MemberService {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		// System.out.println(getLowerMemberCount(17));
		// System.out.println(getBetTotalAmount(17));
		//System.out.println(getWinAmount(20)); //ceshi
		//System.out.println(getLevelCount(22));
		//Member m0 = (Member) HibernateTemple.load(Member.class, 22);
		//List<Member> ms = getTopMembers(m0);
		//System.out.println(ms.size());
		//System.out.println(ms.get(0).getUsername());
		//System.out.println(checkUsername("s2"));
		//System.out.println(getAllLowerIds(20));
		// "06/19/2012", "06/20/2012"=>6318.409599999987 ||  "06/17/2012", "06/20/2012"=>6407.31
		//System.out.println(searchLowerTreeTotalWinAmountByUserId(20, "06/17/2012", "06/20/2012"));
		//17:36
		//System.out.println(getLowerTreeStatisticsListByUserId(142, "", ""));
		System.out.println(getIsOnline(170));
	}
	/**更新账户余额
	 * @param m
	 * @param amount
	 * @param orderId
	 * @param accountGroup
	 * @param note
	 */
	public static synchronized void updateBalance(Member m, double amount, String orderId, int accountGroup,
			String note){
		double newBalance = getBalance(m.getId());
		m.setBalance(newBalance + amount);
		HibernateTemple.update(m);
		new AccountRecord().insert(m, orderId, accountGroup, amount, note);
	}
	/**获取账户余额
	 * @param userId
	 * @return
	 */
	public static double getBalance(int userId){
		double amount = 0;
		Double b = (Double) HibernateTemple.getUniqueResult("select balance from Member where id="+userId);
		amount = b==null ? -1 : b;
		return amount;
	}
	public static int getIsSessionOnline(int userId){
		if(HttpSessionAttributeListenerImpl.getSessionUserIds().contains(userId)){
			return 1;
		}
		return 0;
	}
	/**在线状态
	 * @param userId
	 * @return
	 */
	public static int getIsOnline(int userId){
		return (Integer) HibernateTemple.getUniqueResult("select isOnline from Member where id="+userId);
	}
	//标识登录
	public static void setLoginFlag(int userId){
		JdbcTemple.executeUpdate("update Member set isOnline=1 where id="+userId);
	}
	
	//消除标识登录
	public static void setLogoutFlag(int userId){
		JdbcTemple.executeUpdate("update Member set isOnline=0 where id="+userId);
	}
	
	/**同步更新会员数据
	 * @param m
	 */
	public static synchronized void updateMember(Member m){
		Session s = HibernateSessionFactory.getSession();
		s.beginTransaction();
		s.update(m);
		s.getTransaction().commit();
		s.close();
	}
	
	/**获取下级树账户统计列表
	 * @param userId
	 * @return
	 */
	public static List<MemberStatistics> getLowerTreeStatisticsListByUserId(int userId, String dateFrom, String dateTo){
		List<MemberStatistics> list = new ArrayList<MemberStatistics>();
		List<Integer> uidList = new ArrayList<Integer>();
		if(userId <=0 ){
			uidList = (List<Integer>) HibernateTemple.list("select id from Member");
		}else{
			uidList = getLowerTreeUserIds(userId);
		}
		for(int uid : uidList){
			MemberStatistics mst = getLowerTreeStatisticByUserId(uid, dateFrom, dateTo);
			/*if(mst.getTotalWinAmount() <= 0){
				continue;  //去掉盈亏0
			}*/
			list.add(mst);
		}
		for(MemberStatistics mst : list){
			System.out.println(mst.toString());
		}
		return list;
	}
	
	/**获取账户统计列表
	 * @param userId
	 * @return
	 */
	public static List<MemberStatistics> getStatisticsListByUserId(int userId, String dateFrom, String dateTo){
		List<MemberStatistics> list = new ArrayList<MemberStatistics>();
		List<Integer> uidList = new ArrayList<Integer>();
		if(userId <=0 ){
			uidList = (List<Integer>) HibernateTemple.list("select id from Member");
		}else{
			uidList = getLowerTreeUserIds(userId);
		}
		for(int uid : uidList){
			MemberStatistics mst = getStatisticByUserId(uid, dateFrom, dateTo);
			list.add(mst);
		}
		for(MemberStatistics mst : list){
			System.out.println(mst.toString());
		}
		return list;
	}
	
	/**获取某个会员账变统计对象 (不累计下级数据) 
	 * @param userId
	 * @param dateFrom
	 * @param dateTo
	 * @return
	 */
	public static MemberStatistics getStatisticByUserId(int userId, String dateFrom, String dateTo){

	    String idString = userId+""; //改为不累计下级数据
		Member m = (Member) HibernateTemple.load(Member.class, userId);
		MemberStatistics mst = new MemberStatistics();
		//mst.setLowerMemberCount(lowerCount);
		mst.setOwnerId(m.getId());
		mst.setOwnerName(m.getUsername());
		mst.setTotalBetAmount(searchLowerTreeTotalBetAmountByUserId(idString, dateFrom, dateTo));
		mst.setTotalReturnAmount(searchLowerTreeTotalReturnAmountByUserId(idString, dateFrom, dateTo));
		mst.setTotalRewardAmout(searchLowerTreeTotalRewardAmountByUserId(idString, dateFrom, dateTo));
		mst.setTotalWinAmount(searchLowerTreeTotalWinAmountByUserId(idString, dateFrom, dateTo));
		mst.setTotalRecharge(searchLowerTreeTotalRechargeByUserId(idString, dateFrom, dateTo));
		mst.setTotalTixian(searchLowerTreeTotalTixianByUserId(idString, dateFrom, dateTo));
		return mst;
	}
	
	/**获取某个会员账变统计对象 (不累计下级数据) 
	 * @param userId
	 * @param dateFrom
	 * @param dateTo
	 * @return
	 */
	public static MemberStatistics getLowerTreeStatisticByUserId(int userId, String dateFrom, String dateTo){
		List<Integer> uidList = getLowerTreeUserIds(userId);
		String idString = ""; //下级树id
		int lowerCount = uidList.size();
		for(int i=0; i<uidList.size(); i++){
			if(i==0){
				idString = uidList.get(i)+"";
			}else{
				idString += ","+uidList.get(i);
			}
		}
		idString = userId+""; //改为不累计下级数据
		Member m = (Member) HibernateTemple.load(Member.class, userId);
		MemberStatistics mst = new MemberStatistics();
		mst.setLowerMemberCount(lowerCount);
		mst.setOwnerId(m.getId());
		mst.setOwnerName(m.getUsername());
		mst.setTotalBetAmount(searchLowerTreeTotalBetAmountByUserId(idString, dateFrom, dateTo));
		mst.setTotalReturnAmount(searchLowerTreeTotalReturnAmountByUserId(idString, dateFrom, dateTo));
		mst.setTotalRewardAmout(searchLowerTreeTotalRewardAmountByUserId(idString, dateFrom, dateTo));
		mst.setTotalWinAmount(searchLowerTreeTotalWinAmountByUserId(idString, dateFrom, dateTo));
		mst.setTotalRecharge(searchLowerTreeTotalRechargeByUserId(idString, dateFrom, dateTo));
		mst.setTotalTixian(searchLowerTreeTotalTixianByUserId(idString, dateFrom, dateTo));
		return mst;
	}
	
	/**获取所有下级树的总提现
	 * @param userId
	 * @param dateFrom 格式06/21/2012 对应格式化101
	 * @param dateTo 格式06/21/2012
	 * @return
	 */
	public static double searchLowerTreeTotalTixianByUserId(String uids, String dateFrom,
			 String dateTo){
		double total = 0;
		String groupSQL = " and groupId in("+AccountRecord.GROUP_USER_WITHDRAW+") ";
		dateFrom = dateFrom==null ? "" : dateFrom;
		dateTo = dateTo==null ? "" : dateTo;
		String dateFromSQL = dateFrom.equals("") ? ""
				: " and CONVERT(varchar(12) , createDate, 101 ) >= '"+dateFrom+"' ";
		String dateToSQL = dateTo.equals("") ? ""
				: " and CONVERT(varchar(12) , createDate, 101 ) <= '"+dateTo+"' ";
		//String uids = getLowerTreeUserIds(userId); //下级树id
		Double temp = (Double) HibernateTemple.getUniqueResult("select SUM(amountIn+amountOut) " +
				" from AccountRecord where ownerId in ("+uids+") " + dateFromSQL + dateToSQL + groupSQL);
		total = temp==null ? 0 : temp;
		total = FormatUtil.formatDecimal(total, 2);
		return total;
	}
	
	/**获取所有下级树的总充值
	 * @param userId
	 * @param dateFrom 格式06/21/2012 对应格式化101
	 * @param dateTo 格式06/21/2012
	 * @return
	 */
	public static double searchLowerTreeTotalRechargeByUserId(String uids, String dateFrom,
			 String dateTo){
		double total = 0;
		String groupSQL = " and groupId in("+AccountRecord.GROUP_RECHARGE_BY_ADMIN+","+
			AccountRecord.GROUP_RECHARGE_BY_ONLINE+","+
			AccountRecord.GROUP_RECHARGE_BY_OTHER+") ";
		dateFrom = dateFrom==null ? "" : dateFrom;
		dateTo = dateTo==null ? "" : dateTo;
		String dateFromSQL = dateFrom.equals("") ? ""
				: " and CONVERT(varchar(12) , createDate, 101 ) >= '"+dateFrom+"' ";
		String dateToSQL = dateTo.equals("") ? ""
				: " and CONVERT(varchar(12) , createDate, 101 ) <= '"+dateTo+"' ";
		//String uids = getLowerTreeUserIds(userId); //下级树id
		Double temp = (Double) HibernateTemple.getUniqueResult("select SUM(amountIn+amountOut) " +
				" from AccountRecord where ownerId in ("+uids+") " + dateFromSQL + dateToSQL + groupSQL);
		total = temp==null ? 0 : temp;
		total = FormatUtil.formatDecimal(total, 2);
		return total;
	}
	
	/**获取所有下级树的总盈亏
	 * @param userId
	 * @param dateFrom 格式06/21/2012 对应格式化101
	 * @param dateTo 格式06/21/2012
	 * @return
	 */
	public static double searchLowerTreeTotalWinAmountByUserId(String uids, String dateFrom,
			 String dateTo){
		double total = 0;
		String groupSQL = " and groupId in("+AccountRecord.GROUP_RETURN+","+
			AccountRecord.GROUP_RETURN_LOWER+","+
			AccountRecord.GROUP_LOTTERY_AFTER_BET+","+
			AccountRecord.GROUP_LOTTERY_BET+","+
			AccountRecord.GROUP_LOTTERY_UNION_BET+","+
			AccountRecord.GROUP_AFTER_BET_CANCEL+","+
			AccountRecord.GROUP_LOTTERY_BET_CANCEL+","+
			AccountRecord.GROUP_LOTTERY_UNION_PAY+","+
			AccountRecord.GROUP_REWARD_PAY+") ";
		dateFrom = dateFrom==null ? "" : dateFrom;
		dateTo = dateTo==null ? "" : dateTo;
		String dateFromSQL = dateFrom.equals("") ? ""
				: " and CONVERT(varchar(12) , createDate, 101 ) >= '"+dateFrom+"' ";
		String dateToSQL = dateTo.equals("") ? ""
				: " and CONVERT(varchar(12) , createDate, 101 ) <= '"+dateTo+"' ";
		//String uids = getLowerTreeUserIds(userId); //下级树id
		Double temp = (Double) HibernateTemple.getUniqueResult("select SUM(amountIn+amountOut) " +
				" from AccountRecord where ownerId in ("+uids+") " + dateFromSQL + dateToSQL + groupSQL);
		total = temp==null ? 0 : temp;
		total = FormatUtil.formatDecimal(total, 2);
		return total;
	}
	
	/**获取所有下级树的总返点
	 * @param userId
	 * @param dateFrom 格式06/21/2012 对应格式化101
	 * @param dateTo 格式06/21/2012
	 * @return
	 */
	public static double searchLowerTreeTotalReturnAmountByUserId(String uids, String dateFrom,
			 String dateTo){
		double total = 0;
		String groupSQL = " and groupId in("+
			AccountRecord.GROUP_RETURN+","+
			AccountRecord.GROUP_RETURN_LOWER+
			") ";
		dateFrom = dateFrom==null ? "" : dateFrom;
		dateTo = dateTo==null ? "" : dateTo;
		String dateFromSQL = dateFrom.equals("") ? ""
				: " and CONVERT(varchar(12) , createDate, 101 ) >= '"+dateFrom+"' ";
		String dateToSQL = dateTo.equals("") ? ""
				: " and CONVERT(varchar(12) , createDate, 101 ) <= '"+dateTo+"' ";
		//String uids = getLowerTreeUserIds(userId); //下级树id
		Double temp = (Double) HibernateTemple.getUniqueResult("select SUM(amountIn+amountOut) " +
				" from AccountRecord where ownerId in ("+uids+") " + dateFromSQL + dateToSQL  + groupSQL);
		total = temp==null ? 0 : temp;
		total = FormatUtil.formatDecimal(total, 2);
		return total;
	}
	
	/**获取所有下级树的总奖金
	 * @param userId
	 * @param dateFrom 格式06/21/2012 对应格式化101
	 * @param dateTo 格式06/21/2012
	 * @return
	 */
	public static double searchLowerTreeTotalRewardAmountByUserId(String uids, String dateFrom,
			 String dateTo){
		double total = 0;
		String groupSQL = " and groupId in("+
			AccountRecord.GROUP_LOTTERY_UNION_PAY+","+
			AccountRecord.GROUP_REWARD_PAY+
			") ";
		dateFrom = dateFrom==null ? "" : dateFrom;
		dateTo = dateTo==null ? "" : dateTo;
		String dateFromSQL = dateFrom.equals("") ? ""
				: " and CONVERT(varchar(12) , createDate, 101 ) >= '"+dateFrom+"' ";
		String dateToSQL = dateTo.equals("") ? ""
				: " and CONVERT(varchar(12) , createDate, 101 ) <= '"+dateTo+"' ";
		//String uids = getLowerTreeUserIds(userId); //下级树id
		Double temp = (Double) HibernateTemple.getUniqueResult("select SUM(amountIn) " +
				" from AccountRecord where ownerId in ("+uids+") " + dateFromSQL + dateToSQL  + groupSQL);
		total = temp==null ? 0 : temp;
		total = FormatUtil.formatDecimal(total, 2);
		return total;
	}
	
	/**获取所有下级树的总投注额
	 * @param userId
	 * @param dateFrom 格式06/21/2012 对应格式化101
	 * @param dateTo 格式06/21/2012
	 * @return
	 */
	public static double searchLowerTreeTotalBetAmountByUserId(String uids, String dateFrom,
			 String dateTo){
		double total = 0;
		String groupSQL = " and groupId in("+
			AccountRecord.GROUP_LOTTERY_AFTER_BET+","+
			AccountRecord.GROUP_LOTTERY_BET+","+
			AccountRecord.GROUP_LOTTERY_UNION_BET+","+
			AccountRecord.GROUP_AFTER_BET_CANCEL+","+
			AccountRecord.GROUP_LOTTERY_BET_CANCEL+
			") ";
		dateFrom = dateFrom==null ? "" : dateFrom;
		dateTo = dateTo==null ? "" : dateTo;
		String dateFromSQL = dateFrom.equals("") ? ""
				: " and CONVERT(varchar(12) , createDate, 101 ) >= '"+dateFrom+"' ";
		String dateToSQL = dateTo.equals("") ? ""
				: " and CONVERT(varchar(12) , createDate, 101 ) <= '"+dateTo+"' ";
		//String uids = getLowerTreeUserIds(userId); //下级树id
		Double temp = (Double) HibernateTemple.getUniqueResult("select SUM(amountIn+amountOut) " +
				" from AccountRecord where ownerId in ("+uids+") " + dateFromSQL + dateToSQL + groupSQL);
		total = temp==null ? 0 : temp;
		total = FormatUtil.formatDecimal(total, 2);
		return total;
	}
	
	/**获取所有下级树的会员id,包括自己的id
	 * @return
	 */
	public static List<Integer> getLowerTreeUserIds(int userId){
		String idString = "";
		List<Integer> totalIds = new ArrayList<Integer>();
		totalIds.add(userId);
		fetchLowers(totalIds, userId);
		for(int i=0; i<totalIds.size(); i++){
			if(i==0){
				idString = totalIds.get(i)+"";
			}else{
				idString += ","+totalIds.get(i);
			}
		}
		System.out.println("idString:"+idString);
		return totalIds;
	}
	
	/**
	 * 获取该用户的直接下级，供递归调用
	 */
	public static void fetchLowers(List<Integer> totalIds, int userId){
		List<Integer> ids = (List<Integer>) HibernateTemple.list("select id from Member where fatherId="+userId);
		for(int id : ids){
			//System.out.println(userId+"--"+id);
			totalIds.add(id);
			fetchLowers(totalIds, id);
		}
	}
	
	/**检查用户名是否存在 
	 * @param username
	 * @return true-存在|false-不存在
	 */
	public static boolean checkUsername(String username){
		boolean results = false;
		String sql = "select * from Member where username = '" + username +"'";
		Connection conn = JdbcTemple.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		rs = JdbcTemple.executeQuery(conn, pstmt, rs, sql);
		try {
			while (rs.next()) {
				results = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcTemple.close(conn, pstmt, rs);
		return results;
	}
	/**
	 * 获取会员级数 顶级会员(无上级)返回0 有一个上级返回1
	 * 
	 * @param uid
	 * @return
	 */
	public static int getLevelCount(int uid) {
		int results = 0;
		int topId = getTopId(uid);
		while (topId > 0) {
			results++;
			topId = getTopId(topId);
		}
		return results;
	}
	
	/**上级会员
	 * @param m
	 * @return
	 */
	public static Member getTopMember(Member m){
		if(m.getFatherId() == 0)
			return null;
		return (Member) HibernateTemple.load(Member.class, m.getFatherId());
	}
	
	/**获取上级会员列表(不包括自己)
	 * @param m
	 * @return
	 */
	public static List<Member> getTopMembers(Member m){
		List<Member> ms = null;
		JdbcUtil jdbc = new JdbcUtil(Database.TYPE_SQLSERVER);
		int fid = m.getFatherId();
		if(fid==0){
			return null;
		}
		while(fid > 0){
			Member m2 = (Member) jdbc.getEntityBySQL(Member.class.getName(), 
					"select * from Member where id="+fid);
			if(m2!=null){
				if(ms == null){
					ms = new ArrayList<Member>();
				}
				ms.add(m2);
				fid = m2.getFatherId();
			}else{
				fid = 0;
			}
		}
		return ms;
	}
	
	/**获取上级ID
	 * @param uid
	 * @return
	 */
	public static int getTopId(int uid) {
		int topId = 0;
		String sql = "select fatherId as topId from Member where id = " + uid;
		Connection conn = JdbcTemple.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		rs = JdbcTemple.executeQuery(conn, pstmt, rs, sql);
		try {
			while (rs.next()) {
				topId = rs.getInt("topId");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcTemple.close(conn, pstmt, rs);
		return topId;
	}

	/**
	 * 盈亏总计
	 * 
	 * @param uid
	 * @return
	 */
	public static Double getWinAmount(int uid) {
		Double amount = 0D;
		String sql = "select SUM(amountIn-amountOut) as amount from AccountRecord where ownerId = "
				+ uid
				+ " and (groupId="
				+ AccountRecord.GROUP_LOTTERY_AFTER_BET
				+ " or groupId="
				+ AccountRecord.GROUP_LOTTERY_BET
				+ " or groupId="
				+ AccountRecord.GROUP_RETURN
				+ " or groupId="
				+ AccountRecord.GROUP_REWARD_PAY + ")";
		Connection conn = JdbcTemple.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		rs = JdbcTemple.executeQuery(conn, pstmt, rs, sql);
		try {
			while (rs.next()) {
				amount = rs.getDouble("amount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcTemple.close(conn, pstmt, rs);
		return amount;
	}

	/**
	 * 获取返点总额
	 * 
	 * @param uid
	 * @return
	 */
	public static Double getTotalReturnAmount(int uid) {
		Double amount = 0D;
		String sql = "select SUM(amountIn) as amount from AccountRecord where ownerId = "
				+ uid + " and groupId=" + AccountRecord.GROUP_RETURN;
		Connection conn = JdbcTemple.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		rs = JdbcTemple.executeQuery(conn, pstmt, rs, sql);
		try {
			while (rs.next()) {
				amount = rs.getDouble("amount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcTemple.close(conn, pstmt, rs);
		return amount;
	}

	/**
	 * 中奖总额
	 * 
	 * @param uid
	 * @return
	 */
	public static Double getTotalReward(int uid) {
		Double amount = 0D;
		String sql = "select SUM(amountIn) as amount from AccountRecord where ownerId = "
				+ uid + " and groupId=" + AccountRecord.GROUP_REWARD_PAY;
		Connection conn = JdbcTemple.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		rs = JdbcTemple.executeQuery(conn, pstmt, rs, sql);
		try {
			while (rs.next()) {
				amount += rs.getDouble("amount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcTemple.close(conn, pstmt, rs);
		return amount;
	}
	
	/** 充值总额
	 * @param uid
	 * @return
	 */
	public static Double getTotalRecharge(int uid) {
		Double amount = 0D;
		String sql = "select SUM(amountIn) as amount from AccountRecord where ownerId = "
				+ uid + " and groupId in (" + AccountRecord.GROUP_RECHARGE_BY_ADMIN  + ", "
				+ AccountRecord.GROUP_RECHARGE_BY_ONLINE + ","
				+ AccountRecord.GROUP_USER_DEPOSIT + ","
				+ AccountRecord.GROUP_RECHARGE_BY_OTHER + ")"
				;
		Connection conn = JdbcTemple.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		rs = JdbcTemple.executeQuery(conn, pstmt, rs, sql);
		try {
			while (rs.next()) {
				amount += rs.getDouble("amount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcTemple.close(conn, pstmt, rs);
		return amount;
	}
	
	/** 提现总额
	 * @param uid
	 * @return
	 */
	public static Double getTotalTiXian(int uid) {
		Double amount = 0D;
		String sql = "select SUM(amountOut) as amount from AccountRecord where ownerId = "
				+ uid + " and groupId in (" + AccountRecord.GROUP_USER_WITHDRAW + ")"
				;
		Connection conn = JdbcTemple.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		rs = JdbcTemple.executeQuery(conn, pstmt, rs, sql);
		try {
			while (rs.next()) {
				amount += rs.getDouble("amount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcTemple.close(conn, pstmt, rs);
		return amount;
	}

	/**
	 * 获取投注总额
	 * 
	 * @param uid
	 * @return
	 */
	public static Double getBetTotalAmount(int uid) {
		Double amount = 0D;
		String sql = "select SUM(amountOut) as amount from AccountRecord where ownerId = "
				+ uid
				+ " and (groupId="
				+ AccountRecord.GROUP_LOTTERY_BET
				+ " or groupId=" + AccountRecord.GROUP_LOTTERY_AFTER_BET + ") ";
		Connection conn = JdbcTemple.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		rs = JdbcTemple.executeQuery(conn, pstmt, rs, sql);
		try {
			while (rs.next()) {
				amount += rs.getDouble("amount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcTemple.close(conn, pstmt, rs);
		return amount;
	}

	/**
	 * 获取下级会员数量
	 * 
	 * @param uid
	 *            会员id
	 * @return
	 */
	public static int getLowerMemberCount(int uid) {
		int count = 0;
		String sql = "select count(*) as num from Member where fatherId = "
				+ uid;
		Connection conn = JdbcTemple.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		rs = JdbcTemple.executeQuery(conn, pstmt, rs, sql);
		try {
			while (rs.next()) {
				count = rs.getInt("num");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcTemple.close(conn, pstmt, rs);
		return count;
	}

}
